package com.pms.platform.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;

import com.pms.platform.dto.User;
import com.pms.platform.resource.ResourceManager;

public class UserDao {


	/**
	 * @param args
	 */
	private static final String TRACE_ID = "UserDao";



	protected final static String	SQL_SELECT	 = "SELECT  UserId,  Vendor_Code, Branch_Code,  LoginId,  Password,  Title,  Name,  MobileNo,  EmailId,  CityId,  StateId,  CountryId,  Address,  ZipCode,  Status,  Created_Date,  IsDeleted FROM USER "; 
	/** 
	 * SQL INSERT statement for this table
	 */
	protected static final String SQL_INSERT = "INSERT INTO User( Vendor_Code, Branch_Code,  LoginId,  Password,  Title,  Name,  MobileNo,  EmailId,  CityId,  StateId,  CountryId,  Address,  ZipCode,  Status,  Created_Date,  IsDeleted) VALUES (?, ?, ? , ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

	/** 
	 * SQL UPDATE statement for this table
	 */
	protected static final String SQL_UPDATE = "UPDATE User  SET Vendor_Code = ?, Branch_Code = ?,  LoginId = ?,  Password = ?,  Title = ?,  Name = ?,  MobileNo = ?,  EmailId = ?,  CityId = ?,  StateId = ?,  CountryId = ?,  Address = ?,  ZipCode = ?,  Status = ?,  Created_Date = ?,  IsDeleted= ? where UserId = ?";

	protected static final int COLUMN_USERID=1 ;
	protected static final int COLUMN_VENDOR_CODE=2 ;
	protected static final int COLUMN_BRANCH_CODE= 3;
	protected static final int COLUMN_LOGINID=4 ;
	protected static final int COLUMN_PASSWORD= 5;
	protected static final int COLUMN_TITLE= 6;
	protected static final int COLUMN_NAME= 7;
	protected static final int COLUMN_MOBILENO= 8;
	protected static final int COLUMN_EMAILID=9 ;
	protected static final int COLUMN_CITYID= 10;
	protected static final int COLUMN_STATEID= 11;
	protected static final int COLUMN_COUNTRYID= 12;
	protected static final int COLUMN_ADDRESS=13 ;
	protected static final int COLUMN_ZIPCODE= 14;
	protected static final int COLUMN_STATUS= 15;
	protected static final int COLUMN_CREATED_DATE= 16;
	protected static final int COLUMN_ISDELETED =17;

	protected static final int NUMBER_OF_COLUMNS = 17;

	/**
	 *  Use of SingleTon Pattern
	 *  
	 */

	private static UserDao userDao= null;

	private UserDao(){

	}

	public static UserDao getInstance(){
		if(userDao==null){

			userDao = new UserDao();
		}
		return userDao;
	}

	/**
	 * Inserts a new row in the CacheTrips table.
	 */
	public  long insert(User dto, Connection conn)throws SQLException {

		PreparedStatement stmt = null;

		long id = -1;
		stmt = conn.prepareStatement( SQL_INSERT, stmt.RETURN_GENERATED_KEYS );
stmt.setString( COLUMN_VENDOR_CODE-1, dto.getVendor_Code() );      
		stmt.setString( COLUMN_BRANCH_CODE-1, dto.getBranch_Code()  );
		stmt.setString( COLUMN_LOGINID-1, dto.getLoginId()  );
		stmt.setString( COLUMN_PASSWORD-1, dto.getPassword()  );
		stmt.setString( COLUMN_TITLE-1, dto.getTitle()  );
		stmt.setString( COLUMN_NAME-1, dto.getName()  );
		stmt.setString( COLUMN_MOBILENO-1, dto.getMobileNo()  );
		stmt.setString( COLUMN_EMAILID-1, dto.getEmailId()  );
		stmt.setLong( COLUMN_CITYID-1, dto.getCityId()  );
		stmt.setLong( COLUMN_STATEID-1, dto.getStateId()  );
		stmt.setLong( COLUMN_COUNTRYID-1, dto.getCountryId()  );
		stmt.setString( COLUMN_ADDRESS-1, dto.getAddress()  );
		stmt.setString( COLUMN_ZIPCODE-1, dto.getZipCode()  );
		stmt.setString( COLUMN_STATUS-1, dto.getStatus()  );
		stmt.setTimestamp(COLUMN_CREATED_DATE-1,  new Timestamp( (dto.getCreated_Date()!=null) ? dto.getCreated_Date().getTime():new Date().getTime() ));
		stmt.setBoolean( COLUMN_ISDELETED-1, dto.isDeleted()  );


		System.out.println("Executing " + SQL_INSERT + " with DTO: " + dto );
		stmt.execute();
		ResultSet rs=stmt.getGeneratedKeys();
		while(rs.next()){
			id= rs.getLong(1);
		}
		int rows = stmt.getUpdateCount();
		System.out.println(rows + " rows affected" );
		return id;
	}

	public  long insert(User dto) {
		Connection conn = null;
		try {
			conn = ResourceManager.getConnection(TRACE_ID);
			return insert(dto, conn);
		} catch (SQLException _e) {
			_e.printStackTrace();
		} catch (Exception _e) {
			_e.printStackTrace();
		} finally {
			ResourceManager.freeConnection(conn,TRACE_ID);
		}
		return -1;
	}


	/**
	 * Updates a single row in the User table.
	 */
	public  void update(long UserId, User dto) {
		Connection conn = null;
		try {
			conn = ResourceManager.getConnection(TRACE_ID);
			update(UserId, dto, conn);
		} catch (SQLException _e) {
			_e.printStackTrace();
		} catch (Exception _e) {
			_e.printStackTrace();
		} finally {
			ResourceManager.freeConnection(conn,TRACE_ID);
		}
	}

	/**
	 * Updates a single row in the User table.
	 */
	public  void update(long userId, User dto, Connection conn) throws SQLException {

		PreparedStatement stmt = null;
		try{
			stmt = conn.prepareStatement( SQL_UPDATE );
			stmt.setString( COLUMN_VENDOR_CODE-1, dto.getVendor_Code() );      
			stmt.setString( COLUMN_BRANCH_CODE-1, dto.getBranch_Code()  );
			stmt.setString( COLUMN_LOGINID-1, dto.getLoginId()  );
			stmt.setString( COLUMN_PASSWORD-1, dto.getPassword()  );
			stmt.setString( COLUMN_TITLE-1, dto.getTitle()  );
			stmt.setString( COLUMN_NAME-1, dto.getName()  );
			stmt.setString( COLUMN_MOBILENO-1, dto.getMobileNo()  );
			stmt.setString( COLUMN_EMAILID-1, dto.getEmailId()  );
			stmt.setLong( COLUMN_CITYID-1, dto.getCityId()  );
			stmt.setLong( COLUMN_STATEID-1, dto.getStateId()  );
			stmt.setLong( COLUMN_COUNTRYID-1, dto.getCountryId()  );
			stmt.setString( COLUMN_ADDRESS-1, dto.getAddress()  );
			stmt.setString( COLUMN_ZIPCODE-1, dto.getZipCode()  );
			stmt.setString( COLUMN_STATUS-1, dto.getStatus()  );
			stmt.setTimestamp(COLUMN_CREATED_DATE-1,  new Timestamp( (dto.getCreated_Date()!=null) ? dto.getCreated_Date().getTime():new Date().getTime() ));
			stmt.setBoolean( COLUMN_ISDELETED-1, dto.isDeleted()  );
			
			stmt.setLong(NUMBER_OF_COLUMNS,userId);		

			stmt.executeUpdate();	

		}catch(Exception e){
			e.printStackTrace();
		}
	}

	/**
	 * Populating DTO For User table.
	 */
	protected  void populateDto(User dto, ResultSet rs) throws SQLException{
		
		dto.setUserId( rs.getLong(  COLUMN_USERID));
		dto.setVendor_Code(rs.getString( COLUMN_VENDOR_CODE));      
		dto.setBranch_Code( rs.getString(  COLUMN_BRANCH_CODE));
		dto.setLoginId( rs.getString(  COLUMN_LOGINID));
		dto.setPassword( rs.getString(  COLUMN_PASSWORD));
		dto.setTitle( rs.getString(  COLUMN_TITLE));
		dto.setName( rs.getString(  COLUMN_NAME));
		dto.setMobileNo( rs.getString(  COLUMN_MOBILENO));
		dto.setEmailId( rs.getString(  COLUMN_EMAILID));
		dto.setCityId( rs.getLong(  COLUMN_CITYID));
		dto.setStateId( rs.getLong(  COLUMN_STATEID));
		dto.setCountryId( rs.getLong(  COLUMN_COUNTRYID));
		dto.setAddress(rs.getString(  COLUMN_ADDRESS));
		dto.setZipCode( rs.getString(  COLUMN_ZIPCODE));
		dto.setStatus( rs.getString(  COLUMN_STATUS));
		dto.setCreated_Date( rs.getTimestamp(  COLUMN_CREATED_DATE));
		dto.setDeleted( rs.getBoolean(  COLUMN_ISDELETED));
	}

	/**
	 * Fetching ResultSet From User table.
	 */
	protected  User[] fetchMultiResults(ResultSet rs) throws SQLException{
		Collection resultList = new ArrayList();
		while (rs.next()) {
			User dto = new User();
			populateDto( dto, rs);
			resultList.add( dto );
		}
		User ret[] = new User[ resultList.size() ];
		resultList.toArray( ret );
		return ret;
	}

	public  User[] findByDynamicSelect(String sql, Object[] sqlParams,Connection conn) throws SQLException{
		PreparedStatement stmt = null;
		ResultSet rs = null;
		final String SQL = sql;

		System.out.println( "Executing " + SQL );
		stmt = conn.prepareStatement( SQL );
		for (int i=0; sqlParams!=null && i<sqlParams.length; i++ ) {
			stmt.setObject( i+1, sqlParams[i] );
		}
		rs = stmt.executeQuery();
		return fetchMultiResults(rs);
	}

	public  User[] findByDynamicSelect(String sql, Object[] sqlParams) {
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;

		try {
			conn = ResourceManager.getConnection(TRACE_ID);

			final String SQL = sql;

			System.out.println("Executing " + SQL);
			stmt = conn.prepareStatement(SQL);
			for (int i = 0; sqlParams != null && i < sqlParams.length; i++) {
				stmt.setObject(i + 1, sqlParams[i]);
			}

			rs = stmt.executeQuery();

			return fetchMultiResults(rs);
		} catch (SQLException _e) {
			_e.printStackTrace();
		} catch (Exception _e) {
			_e.printStackTrace();
		} finally {
			try {
				stmt.close();
				rs.close();
			} catch (Exception e) {
			}
			ResourceManager.freeConnection(conn,TRACE_ID);
		}
		return null;
	}
	public  void delete(long userId) {
		User _user= findByPrimaryKey(userId);
		_user.setDeleted(true);
		update(userId, _user);
	}

	/**
	 * Selects all data from the User table based on UserId.
	 */

	public  User findByPrimaryKey(long UserId){

		User ret[] = findByDynamicSelect( SQL_SELECT + " WHERE UserId = ? AND IsDeleted = ?", new Object[] {  new Long(UserId),new  Boolean(false) });
		return ret.length==0 ? null : ret[0];

	}
	public  User[] findAllUsersForStudio(long studioId){

		return findByDynamicSelect( SQL_SELECT + " WHERE Vendor_Code = ? AND IsDeleted = ? ORDER BY FirstName", new Object[] {  new Long(studioId),new  Boolean(false) });

	}
	/**
	 * Returns all rows from the User table that match the criteria 'Login =
	 * :login'.
	 */
	public  User findWhereLoginIdEquals(String login) {
		User[] ret = findByDynamicSelect(SQL_SELECT
				+ " WHERE LoginId = ? AND IsDeleted = ?",
				new Object[] { new String(login), new  Boolean(false) });
		return ret.length == 0 ? null : ret[0];
	}
	public  User findWhereLoginIdEqualsForStudio(long studioId,String login) {
		User[] ret = findByDynamicSelect(SQL_SELECT
				+ " WHERE Vendor_Code =? AND LoginId = ? AND IsDeleted = ?",
				new Object[] {new Long(studioId), new String(login), new  Boolean(false) });
		return ret.length == 0 ? null : ret[0];
	}
	/**
	 * Returns all rows from the User table that match the criteria 'Login =
	 * :login', 'password = :password'.
	 */
	public  User findWhereLoginIdAndPasswordEquals(String login,String password) {
		User[] ret = findByDynamicSelect(SQL_SELECT
				+ " WHERE LoginId = ? AND password=? AND IsDeleted = ? ",
				new Object[] { new String(login),new String(password), new  Boolean(false) });
		return ret.length == 0 ? null : ret[0];
	}
	public  User[] findAll() {
		return findByDynamicSelect(SQL_SELECT + " WHERE IsDeleted = ? ORDER BY FirstName",
				new Object[] { new Boolean(false) });
	}

}
